package com.nj.dms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.nj.dms.bean.Book;
import com.nj.dms.bean.User;
import com.nj.dms.dao.BookDao;
import com.nj.dms.util.DbUtil;

public class BookDaoImpl implements BookDao {

	@Override
	public boolean addBook(Book book) {
		Connection con = DbUtil.dbOpen();
		PreparedStatement prstt = null;
		int i = 0;
		String sql = "insert into book(name,author,price,publisher,description)" + "values(?,?,?,?,?);";
		try {
			prstt = con.prepareStatement(sql);
			prstt.setString(1, book.getName());
			prstt.setString(2, book.getAuthor());
			prstt.setDouble(3, book.getPrice());
			prstt.setString(4, book.getPublisher());
			prstt.setString(5, book.getDescription());
			i = prstt.executeUpdate();

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, prstt);
		}

		return i > 0 ? true : false;
	}

	@Override
	public boolean deleteBook(int id) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean deleteAllBook(String[] ids) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean updateBook(Book book) {
		Connection con = DbUtil.dbOpen();
		int i = 0;
		PreparedStatement pst = null;
		String sql = "update book set name=?,author=?,price=?,publisher=?,description=? where id=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, book.getName());
			pst.setString(2, book.getAuthor());
			pst.setDouble(3, book.getPrice());
			pst.setString(4, book.getPublisher());
			pst.setString(5, book.getDescription());
			pst.setInt(6, book.getId());
			i = pst.executeUpdate();

		} catch (Exception e) {
			e.getMessage();
		}
		return i > 0 ? true : false;

	}

	/**
	 * 根据图书ID查到一本书，并返回一个Book对象
	 */
	@Override
	public Book findBook(int id) {
		Book b = new Book();
		Connection con = DbUtil.dbOpen();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = con.prepareStatement("select * from book where id=?");
			pst.setInt(1, id);
			rs = pst.executeQuery();
			while (rs.next()) {

				b.setId(rs.getInt("id"));
				b.setName(rs.getString("name"));
				b.setPrice(rs.getDouble("price"));
				b.setPublisher(rs.getString("publisher"));
				b.setAuthor(rs.getString("author"));
				b.setDescription(rs.getString("description"));
			}

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, pst, rs);
		}

		return b;
	}

	/**
	 * 查询所有的图书
	 */
	@Override
	public List<Book> findBooks() {
		// 1 获取数据库的Connection对象
		List<Book> listBooks = new ArrayList();
		Connection conn = DbUtil.dbOpen();
		// 2定义一个PreparedStatement类的对象pstmt
		PreparedStatement pstmt = null;
		// 3定义一个ResultSet类的对象rs返回查询的结果集
		ResultSet rs = null;
		// 准备增加的sql语句，并作为参数参数传入preparedStatent对象
		String sqlstr = "select * from book;";
		try {
			pstmt = conn.prepareStatement(sqlstr);
			// 调用PreparedStatement类的executeQuery，返回查询结果记录集合
			rs = pstmt.executeQuery();
			// 通过循环获取每一条记录的内容，并插入到List集合
			while (rs.next()) {
				// 读取当前一条记录内容
				Book b = new Book();
				b.setId(rs.getInt("id"));
				b.setName(rs.getString("name"));
				b.setPrice(rs.getDouble("price"));
				b.setPublisher(rs.getString("publisher"));
				b.setAuthor(rs.getString("author"));
				b.setDescription(rs.getString("description"));
				// 保存在List集合
				listBooks.add(b);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt, rs);
		}
		// 返回查询的用户信息
		return listBooks;
	}

	@Override
	public List<Book> findBookByPage(int beginIndex, int maxSize) {
		Connection con = DbUtil.dbOpen();
		PreparedStatement prtt = null;
		ResultSet ret = null;
		List<Book> listBooks = new ArrayList();
		// mysql获取指定页的记录数的标准sql语句
		// select * from book limit (开始记录号-1)，每一页显示的最大记录个数
		String sql = "SELECT * FROM book LIMIT ?,?;";
		try {
			prtt = con.prepareStatement(sql);
			prtt.setInt(1, beginIndex);
			prtt.setInt(2, maxSize);
			ret = prtt.executeQuery();
			while (ret.next()) {
				Book b = new Book();
				b.setId(ret.getInt("id"));
				b.setName(ret.getString("name"));
				b.setPrice(ret.getDouble("price"));
				b.setPublisher(ret.getString("publisher"));
				b.setAuthor(ret.getString("author"));
				b.setDescription(ret.getString("description"));
				listBooks.add(b);
			}

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, prtt, ret);
		}
		return listBooks;
	}

	@Override
	public int countBooks() {
		Connection con = DbUtil.dbOpen();
		PreparedStatement prtt = null;
		ResultSet rs = null;
		int recordCnt = 0;
		// 获取表的总的记录数的标准sql语句
		// select count(*) from book
		// select count(字段名) from book 返回一个整数
		String sql = "SELECT count(id) FROM book ;";
		try {
			prtt = con.prepareStatement(sql);
			rs = prtt.executeQuery();
			if (rs.next()) {
				// 返回的记录总数是一个整数int
				recordCnt = rs.getInt(1);
			}

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, prtt, rs);
		}
		return recordCnt;
	}

	public static void main(String[] args) {
		// 通过main()方法测试Dao层的方法
		BookDao bookDao = new BookDaoImpl();

		Book b1 = new Book();
		b1.setName("SSH框架整合");
		b1.setAuthor("张三");
		b1.setPrice(68.50);
		b1.setPublisher("清华大学出版社");
		b1.setDescription("这是一本JavaWeb开发的高级框架整合知识的书！");

		if (bookDao.addBook(b1)) {
			System.out.println("图书增加成功！ - BookDaoImpl");
		} else {
			System.out.println("图书增加失败！ - BookDaoImpl");
		}

	}

}
